In [2]:
%load_ext sql
%sql sqlite://
Out[2]:
Давайте создадим таблицу, заполним ее и сделаем какой-нибудь запрос!
In [4]:
%%sql drop table if exists product;
create table product(
pname varchar primary key, -- имя продукта
price money, -- цена продукта
category varchar, -- категория
manufacturer varchar NOT NULL -- производитель
);
insert into product values('Пряник', 19.99, 'Еда', 'Тульский пряник');
insert into product values('Тетрадь', 29.99, 'Канцелярия', 'Эксмо');
insert into product values('Печенье', 149.99, 'Еда', 'WagonWiels');
insert into product values('Планшет', 203.99, 'Техника', 'Zua');
Out[4]:
Посмотрим на полученную таблицу.
In [5]:
%sql select * from product;
Out[5]:
product(pname, price, category, manufacturer)
Подчеркнутое множество атрибутов формирует ключ.
product(pname, price, category, manufacturer)
SELECT <Множество атрибутов>
FROM <список таблиц и условие на соединение>
WHERE <список условий>
Это простейший SELECT-FROM-WHERE (SFW) блок. Давайте посмотрим на примерах!
In [6]:
%%sql
SELECT * from Product
WHERE category = 'Канцелярия' and manufacturer = 'Эксмо'
Out[6]:
Посмотрим на примеры проекции, то есть получим только несколько атрибутов запроса.
In [9]:
%%sql
SELECT Pname, Price, Manufacturer
FROM Product;
Out[9]:
Answer(pname, price, manufacturer)
In [14]:
%%sql
SELECT Pname, Price, Manufacturer
FROM Product
WHERE category='Еда';
Out[14]:
In [16]:
%%sql
SELECT
p.manufacturer, p.pname, p.price
FROM
(SELECT distinct p0.category
FROM Product p0
WHERE p0.price < 20.00) cp, -- this is a nested query!
Product p
WHERE
p.category = cp.category and p.price > 20.00
Out[16]:
In [17]:
%sql select * from product;
Out[17]:
In [19]:
%%sql
SELECT * FROM product
where category LIKE '%да%'
Out[19]:
In [20]:
%sql SELECT category from product;
Out[20]:
In [21]:
%%sql
-- чтобы убрать дубли используйте слово DISTINCT
SELECT DISTINCT category from product;
Out[21]:
In [13]:
%%sql
-- сортировка результатов
-- сортировка по умолчанию - ascending
SELECT pname, price, manufacturer
FROM Product
WHERE price > 50
ORDER BY price, pname
Out[13]:
In [22]:
%%sql
-- сортировка результатов
-- тип сортировки каждого компонента определяется индивидуально
SELECT price, manufacturer
FROM Product
ORDER BY manufacturer ASC, price DESC
Out[22]:
In [32]:
%%sql
drop table if exists product; -- This needs to be dropped if exists, see why further down!
drop table if exists company;
create table company (
cname varchar primary key, -- company name uniquely identifies the company.
stockprice money, -- stock price is in money
country varchar); -- country is just a string
insert into company values ('Тульский пряник', 25.0, 'Россия');
insert into company values ('Эксмо', 65.0, 'Россия');
insert into company values ('Zua', 15.0, 'Китай');
Out[32]:
In [33]:
%sql select * from company;
Out[33]:
Назвнание компании в product ссылается на поле cnma из таблицы company:
foreign key (manufacturer) references company(cname)
In [34]:
%%sql drop table if exists product;
pragma foreign_keys = ON; -- WARNING by default off in sqlite
create table product(
pname varchar primary key, -- name of the product
price money, -- price of the product
category varchar, -- category
manufacturer varchar, -- manufacturer
foreign key (manufacturer) references company(cname));
insert into product values('Пряник', 19.99, 'Еда', 'Тульский пряник');
insert into product values('Тетрадь', 29.99, 'Канцелярия', 'Эксмо');
insert into product values('Печенье', 149.99, 'Еда', 'Тульский пряник');
insert into product values('Планшет', 203.99, 'Техника', 'Zua');
Out[34]:
Внешние ключи являются ограничениями таблицы
Что случится при вставке компании, которой не в таблице company?
In [35]:
try:
%sql insert into product values('MultiTouch', 203.99, 'Household', 'Google');
except Exception as e:
print (e)
print ("Rejected!")
In [36]:
%%sql
-- the update is rejected!
select * from product;
Out[36]:
Первый вариант (default)- Удаление запрещено
In [37]:
try:
%sql delete from company where cname = 'Zua';
except Exception as e:
print (e)
print ("Disallowed!")
Второй вариант: удалить все продукты, принадлежащие компании, которую мы удаляем
Необходимо изменить опеределение в create table:
foreign key (manufacturer) references company(cname) on delete cascade
Теперь, когда строка компании удалена, все продукты с указанной компанией, будут также удалены.
In [31]:
%%sql
SELECT pname, price
FROM product, company
where manufacturer=cname and country='Россия' and price <= 200;
Out[31]:
Другой вариант написания запроса.
In [38]:
%%sql -- Часть 1: выбрать все компании из России
SELECT distinct cname -- нужна ли нам уникальность?
from company where country='Россия';
Out[38]:
In [39]:
%%sql -- Part 2: Продукты до 200
select distinct pname, price, manufacturer
from product
where price <= 200;
Out[39]:
In [40]:
%%sql -- Объединение как cross join
SELECT *
FROM
(SELECT DISTINCT pname, price, manufacturer
FROM product
WHERE price <= 200) CheapProducts,
(SELECT DISTINCT cname
FROM company
WHERE country='Россия') JapaneseProducts;
Out[40]:
In [41]:
%%sql
-- Фильтруем cross join
SELECT DISTINCT pname, price
FROM
(SELECT DISTINCT pname, price, manufacturer
FROM product
WHERE price <= 200) CheapProducts,
(SELECT distinct cname
FROM company
WHERE country='Россия') JapaneseProducts
WHERE cname = manufacturer;
Out[41]:
In [42]:
%%sql -- duplicate answer
SELECT Country
FROM Product, Company
WHERE Manufacturer=CName AND Category='Еда';
Out[42]:
In [ ]:
In [ ]: